unit movGrales;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Mask, ToolEdit, SqlExpr, ExtCtrls, FMTBcd, DB,
  ComCtrls, ToolWin, ImgList, Provider, DBClient, DBLocal, DBLocalS, Grids,
  DBGrids, RXDBCtrl, Buttons;

type
  TfrmMovGrales = class(TForm)
    lblFechaIni: TLabel;
    edtFechaIni: TDateEdit;
    lblFechaFin: TLabel;
    edtFechaFin: TDateEdit;
    Label1: TLabel;
    lblCompra: TLabel;
    Label2: TLabel;
    lblPagoOpe: TLabel;
    Label3: TLabel;
    lblPagoSub: TLabel;
    Label4: TLabel;
    lblIndirectos: TLabel;
    Shape1: TShape;
    Label5: TLabel;
    lblTotal: TLabel;
    ToolBar1: TToolBar;
    ToolButton1: TToolButton;
    ToolButton2: TToolButton;
    dSet: TSQLDataSet;
    ImageList1: TImageList;
    RxDBGrid1: TRxDBGrid;
    ToolButton5: TToolButton;
    ToolButton6: TToolButton;
    ToolButton7: TToolButton;
    cdsRegistros: TSQLClientDataSet;
    sRegistros: TDataSource;
    cdsRegistrosFECHA: TSQLTimeStampField;
    cdsRegistrosCOMPRAS: TFloatField;
    cdsRegistrosOPERARIOS: TFloatField;
    cdsRegistrosSUBCONTRATISTAS: TFloatField;
    cdsRegistrosINDIRECTOS: TFloatField;
    cdsRegistrosINICIO: TSQLTimeStampField;
    cdsRegistrosFIN: TSQLTimeStampField;
    cdsRegistrosTOTAL: TFloatField;
    btnCargar: TSpeedButton;
    ToolButton3: TToolButton;
    ToolButton4: TToolButton;
    ToolButton8: TToolButton;
    ToolButton9: TToolButton;
    procedure btnCargar2Click(Sender: TObject);
    procedure ToolButton1Click(Sender: TObject);
    procedure ToolButton6Click(Sender: TObject);
    procedure RxDBGrid1TitleClick(Column: TColumn);
    procedure ToolButton3Click(Sender: TObject);
    procedure ToolButton8Click(Sender: TObject);
  private
    FflCompra: Double;
    FflSubcontr: Double;
    FflOperario: Double;
    FflIndi: Double;
    function recuperoCompras (ds: TSQLDataSet) : Double;
    function recuperoPagosOperarios (ds: TSQLDataSet) : Double;
    function recuperoPagosSub (ds: TSQLDataSet) : Double;
    function recuperoIndirectos (ds: TSQLDataSet) : Double;
    procedure SetflCompra(const Value: Double);
    procedure SetflIndi(const Value: Double);
    procedure SetflOperario(const Value: Double);
    procedure SetflSubcontr(const Value: Double);
  published
    property flCompra : Double read FflCompra write SetflCompra;
    property flOperario : Double read FflOperario write SetflOperario;
    property flSubcontr : Double read FflSubcontr write SetflSubcontr;
    property flIndi : Double read FflIndi write SetflIndi;
  public
    constructor CreateNuevo(AOwner: TComponent);
  end;

var
  frmMovGrales: TfrmMovGrales;

implementation

uses MDatos, Recursos;

{$R *.dfm}
constructor TfrmMovGrales.CreateNuevo(AOwner: TComponent);
begin
  inherited Create(Aowner);
  self.edtFechaIni.Date := IncMonth(now, -1);
  self.edtFechaFin.Date := now;
  self.ShowModal;
end;

procedure TfrmMovGrales.btnCargar2Click(Sender: TObject);
var
  cdsAux: TSQLDataSet;
begin
  cdsAux := TSQLDataSet.Create(self);
  try
    cdsAux.SQLConnection := Datos.Con;
    //Recupero total compras
    self.flCompra := self.recuperoCompras(cdsAux);
    self.lblCompra.Caption := FloatToStrF (flCompra, ffNumber, 15, 2);
    cdsAux.Close;
    //Recupero total pagos a operarios
    self.flOperario := self.recuperoPagosOperarios(cdsAux);
    self.lblPagoOpe.Caption := FloatToStrF (flOperario, ffNumber, 15, 2);
    cdsAux.Close;
    //Recupero total pagos a subcontratistas
    self.flSubcontr := self.recuperoPagosSub(cdsAux);
    self.lblPagoSub.Caption := FloatToStrF (flSubcontr, ffNumber, 15, 2);
    cdsAux.Close;
    //Recupero total pagos a indirectos
    self.flIndi := self.recuperoIndirectos(cdsAux);
    self.lblIndirectos.Caption := FloatToStrF (flIndi, ffNumber, 15, 2);     
    cdsAux.Close;
    //Sumar
    self.lblTotal.Caption := FloatToStrF (flCompra+flOperario+flSubcontr+flIndi, ffNumber, 15, 2);
  finally
    cdsAux.Free;
  end;  
end;

function TfrmMovGrales.recuperoCompras(ds: TSQLDataSet): Double;
begin
  ds.CommandText := 'SELECT sum(RENGLON_COMPRA_NEW.CANTIDAD * '
  +'RENGLON_COMPRA_NEW.UNITARIO*(COMPRA.IVA/100+1) * (1-COMPRA.DESCUENTO/100)) AS TOTAL '
  +'FROM RENGLON_COMPRA_NEW INNER JOIN COMPRA ON (RENGLON_COMPRA_NEW.ID_COMPRA = COMPRA.ID_COMPRA) '
  +'WHERE COMPRA.FECHA >= '''+FormatDateTime('mm/dd/yy', self.edtFechaIni.Date)
  +''' AND COMPRA.FECHA <= '''+FormatDateTime('mm/dd/yy', self.edtFechaFin.Date)+'''';
  ds.Open;
  result := ds.Fields [0].AsFloat;
end;

function TfrmMovGrales.recuperoPagosOperarios(ds: TSQLDataSet): Double;
begin
  ds.CommandText := 'SELECT (sum((PAGO_OPERARIO.ENMANO '
  +'+ PAGO_OPERARIO.CARGASOCIAL) * PAGO_OPERARIO.CANTIDAD) '
  +'+ sum(PAGO_OPERARIO.ADICIONAL)) AS PAGOS FROM PAGO_OPERARIO '
  +'WHERE PAGO_OPERARIO.FECHA >= '''+FormatDateTime('mm/dd/yy', self.edtFechaIni.Date)
  +''' AND PAGO_OPERARIO.FECHA <= '''+FormatDateTime('mm/dd/yy', self.edtFechaFin.Date)+'''';
  ds.Open;
  result := ds.Fields [0].AsFloat;
end;

function TfrmMovGrales.recuperoPagosSub(ds: TSQLDataSet): Double;
begin
  ds.CommandText := 'SELECT sum(PAGO_SUB.ENMANO + PAGO_SUB.CARGASOCIAL) '
  +'FROM PAGO_SUB WHERE PAGO_SUB.FECHA >= '''+FormatDateTime('mm/dd/yy', self.edtFechaIni.Date)
  +''' AND PAGO_SUB.FECHA <= '''+FormatDateTime('mm/dd/yy', self.edtFechaFin.Date)+'''';
  ds.Open;
  result := ds.Fields [0].AsFloat;
end;

function TfrmMovGrales.recuperoIndirectos(ds: TSQLDataSet): Double;
begin
  ds.CommandText := 'SELECT sum(PAGOS_INDIRECTOS.IMPORTE) '
  +'FROM PAGOS_INDIRECTOS WHERE PAGOS_INDIRECTOS.FECHA >= '''+FormatDateTime('mm/dd/yy', self.edtFechaIni.Date)
  +''' AND PAGOS_INDIRECTOS.FECHA <= '''+FormatDateTime('mm/dd/yy', self.edtFechaFin.Date)+'''';
  ds.Open;
  result := ds.Fields [0].AsFloat;
end;

procedure TfrmMovGrales.ToolButton1Click(Sender: TObject);
begin
  self.dSet.CommandText := 'INSERT INTO registro_costos_totales (fecha, compras, operarios'
  +', subcontratistas, indirectos, inicio, fin) VALUES ('''
  +FormatDateTime('mm/dd/yy:hh:nn:ss', now)+''', '+FloatToStr(self.flCompra)
  +', '+FloatToStr(self.flOperario)+', '+FloatToStr(self.FflSubcontr)+', '+FloatToStr(self.flIndi)
  +', '''+FormatDateTime('mm/dd/yy', self.edtFechaIni.Date)+''', '''
  +FormatDateTime('mm/dd/yy', self.edtFechaFin.Date)+''')';
  self.dSet.ExecSQL();
  if not (self.cdsRegistros.State in [dsInactive]) then
    self.cdsRegistros.Refresh;
end;

procedure TfrmMovGrales.SetflCompra(const Value: Double);
begin
  FflCompra := Value;
end;

procedure TfrmMovGrales.SetflIndi(const Value: Double);
begin
  FflIndi := Value;
end;

procedure TfrmMovGrales.SetflOperario(const Value: Double);
begin
  FflOperario := Value;
end;

procedure TfrmMovGrales.SetflSubcontr(const Value: Double);
begin
  FflSubcontr := Value;
end;

procedure TfrmMovGrales.ToolButton6Click(Sender: TObject);
begin
  if self.cdsRegistros.State in [dsInactive] then
    self.cdsRegistros.Open
  else
    self.cdsRegistros.Refresh;
end;

procedure TfrmMovGrales.RxDBGrid1TitleClick(Column: TColumn);
begin
  self.cdsRegistros.IndexFieldNames := Column.FieldName+'; FECHA';
end;

procedure TfrmMovGrales.ToolButton3Click(Sender: TObject);
var
  ds : TSQLDataSet;
begin
  ds := TSQLDataSet.Create(self);
  try
    ds.SQLConnection := Datos.Con;
    ds.CommandText := 'Delete from REGISTRO_COSTOS_TOTALES where FECHA = '''+
    FormatDateTime('mm/dd/yy hh:mm:ss', self.cdsRegistrosFECHA.AsDateTime)+'''';
    ds.ExecSQL();
    self.cdsRegistros.Refresh;
  finally
    ds.Free;
  end;
end;

procedure TfrmMovGrales.ToolButton8Click(Sender: TObject);
begin
  abrirEnExcel(self, self.cdsRegistros, nil, nil, 'RESUMEN GENERAL', 'INTERVALO DEFINIDO: del '
  +DateTimeToStr(self.edtFechaIni.Date)+' al '+DateTimeToStr(self.edtFechaFin.Date));
end;

end.
